package com.camel.base.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.camel.base.mapper.ZsAnswerItemMapper;
import com.camel.base.model.TbColumn;
import com.camel.base.model.TbDatasource;
import com.camel.base.model.ZsAnswerItem;
import com.camel.base.service.TbColumnService;
import com.camel.base.service.TbDatasourceService;
import com.camel.base.mapper.TbDatasourceMapper;
import com.camel.base.service.ZsAnswerItemService;
import com.camel.base.utils.DBUtils;
import com.camel.core.exceptions.CustomException;
import com.camel.core.service.AbstractBaseCommonService;
import org.checkerframework.checker.units.qual.C;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.sql.SQLException;
import java.util.Collection;
import java.util.List;

/**
 * @author 86183
 * @description 针对表【tb_datasource】的数据库操作Service实现
 * @createDate 2023-08-09 11:02:13
 */
@Service
public class TbDatasourceServiceImpl extends AbstractBaseCommonService<TbDatasourceMapper, TbDatasource> implements TbDatasourceService<TbDatasource> {

    @Autowired
    private TbColumnService<TbColumn> columnService;

    @Override
    public List<JSONObject> model(TbDatasource datasource) throws SQLException {
        return new DBUtils().getConnect(datasource).excuteQuery("SELECT * FROM information_schema.schemata");
    }

    @Override
    public List<JSONObject> table(TbDatasource datasource, String schama, JSONObject params) throws SQLException {
        if (datasource.getDbType().equals(1)) {

        }
        if (datasource.getDbType().equals(2)) {
            if (StringUtils.isEmpty(schama)) {
                throw new CustomException("Postgres数据库必须包含模式");
            }
            if (!ObjectUtils.isEmpty(params) || !StringUtils.isEmpty(params.getString("tableName"))) {
                return new DBUtils().getConnect(datasource).excuteQuery("SELECT * FROM pg_tables WHERE schemaname = '" + schama + "' and tablename like '%" + params.getString("tableName") + "%';");
            } else {
                return new DBUtils().getConnect(datasource).excuteQuery("SELECT * FROM pg_tables WHERE schemaname = '" + schama + "';");
            }
        }
        return null;
    }

    @Override
    public List columns(TbDatasource datasource, String schema, String tableName) throws SQLException {


        if (datasource.getDbType().equals(1)) {

        }
        if (datasource.getDbType().equals(2)) {

            if (StringUtils.isEmpty(schema)) {
                throw new CustomException("Postgres数据库必须包含模式");
            }
            List<TbColumn> columns = columnService.columnlist(datasource.getId(), schema, tableName);


            if (!CollectionUtils.isEmpty(columns)) {
                return columns;
            }

            if (StringUtils.isEmpty(tableName)) {
                throw new CustomException("查询列时缺少表名称(tableName)");
            }
            String a = "SELECT C\n" +
                    "\t.relname AS \"tableName\",\n" +
                    "\tCAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS \"tableDesc\",\n" +
                    "\tA.attname AS \"columnName\",\n" +
                    "\tconcat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\\(.*\\)' ) ) AS \"columnType\",\n" +
                    "\t( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS \"descript\", \n" +
                    "(case A.attname when K.COLUMN_NAME THEN 1 ELSE 0 END) AS \"primaryKey\" " +
                    "FROM\n" +
                    "\tpg_class C,\n" +
                    "\tpg_attribute A,\n" +
                    "\tpg_type T ,\n" +
                    "\tpg_tables B ,\n" +
                    "\tinformation_schema.key_column_usage K \n" +
                    "WHERE\n" +
                    "\tC.relname = '" + tableName + "' \n" +
                    "\tAND A.attnum > 0 \n" +
                    "\tAND A.attrelid = C.oid \n" +
                    "\tAND A.atttypid = T.oid \n" +
                    "\tAND B.schemaname = '" + schema + "'\n" +
                    "\tAND C.relname = B.tablename\n" +
                    "AND K.table_name = '"+ tableName +"'\n" +
                    "\tAND CONSTRAINT_NAME = ALL ( SELECT CONSTRAINT_NAME FROM information_schema.table_constraints WHERE " +
                    "\tTABLE_NAME = '"+ tableName +"' AND constraint_type = 'PRIMARY KEY' )" +
                    "ORDER BY\n" +
                    "\tC.relname DESC,\n" +
                    "\tA.attnum ASC";
            System.out.println(a);
            return new DBUtils().getConnect(datasource).excuteQuery(a);
        }
        return null;
    }

    @Override
    public String primaryKey(TbDatasource datasource, String schama, String tableName) throws SQLException {
        List<JSONObject> jsonObjects = new DBUtils().getConnect(datasource).excuteQuery("SELECT COLUMN_NAME AS \"primaryKey\" \n" +
                "FROM\n" +
                "\tinformation_schema.key_column_usage \n" +
                "WHERE\n" +
                "\tTABLE_NAME = '"+ tableName +"' \n" +
                "\tAND CONSTRAINT_NAME = ALL ( SELECT CONSTRAINT_NAME FROM information_schema.table_constraints WHERE TABLE_NAME = '"+ tableName +"' AND constraint_type = 'PRIMARY KEY' );");
        if(CollectionUtils.isEmpty(jsonObjects)) {
            throw new CustomException("数据表没有主键");
        }
        if(jsonObjects.size() > 1) {
            throw new CustomException("数据包主键过多");
        }
        JSONObject jsonObject = jsonObjects.get(0);
        return jsonObject.getString("primaryKey");
    }
}




